# -*- coding: utf-8 -*-
# File : overview.py
# Author: taoyahui
# Date : 2021/7/15
import sys
sys.path.append("..")
from utils import mysql_util, common_util
from utils.log_util import logger
import datetime
import json
# from loguru import logger
#
# log_path = common_util.get_log_path()
# logger.add(log_path, format="{time:YYYY-MM-DD at HH:mm:ss} | {level} | {message}")



def overview_task():
    """
    总体数据获取
    :return:
    """
    logger.info("overview_task -> 开始获取概览信息")
    leak_inventory_sum()
    patch_sum()
    month_new_patch_sum()
    month_regular_patch_sum()
    leak_inventory_top5()
    inventory_safe_distribute()
    unpatch_top10()
    logger.info("overview_task -> 完成获取概览信息")


def save_new_data(name, value):
    query_sql = f"SELECT count(1) from overview_charts where name = '{name}'"
    res = mysql_util.exec_sql_new(query_sql)
    save_dict = {}
    if res[0][0] == 0:
        # 如果表中没有这个数据
        save_dict['name'] = name
        save_dict['value'] = value
        insert_sql = mysql_util.generate_insert_sql(save_dict, 'overview_charts')
        mysql_util.exec_sql_params(insert_sql, tuple(save_dict.values()))
    else:
        save_dict['value'] = value
        update_content_sql = ""
        for key in save_dict.keys():
            update_content_sql += f"{key}='{save_dict[key]}',"
        update_sql = f"Update overview_charts set {update_content_sql[:-1]} where name = '{name}'"
        mysql_util.exec_sql_new(update_sql)
    pass


def leak_inventory_sum():
    """
    漏洞资产总量
    :return:
    """
    query_sql = "SELECT count(1) from inventory where unpatch_num > 0"
    res = mysql_util.exec_sql_new(query_sql)
    count = res[0][0]
    # print(count)

    save_new_data("leak_inventory_sum", count)


def patch_sum():
    """
    未打补丁数量
    :return:
    """
    query_sql = "select count(distinct cve_id) from inventory_patch"
    res = mysql_util.exec_sql_new(query_sql)
    count = res[0][0]

    save_new_data("unpatch_sum", count)


def month_new_patch_sum():
    """
    本月新增补丁数量
    :return:
    """
    now = datetime.datetime.now()
    this_month_start = datetime.datetime(now.year, now.month, 1)
    query_sql = f"SELECT count(1) from patch where update_time > '{this_month_start}'"
    res = mysql_util.exec_sql_new(query_sql)
    count = res[0][0]
    save_new_data("month_new_patch_sum", count)


def month_regular_patch_sum():
    """
    本月新增转正补丁数量
    :return:
    """
    now = datetime.datetime.now()
    this_month_start = datetime.datetime(now.year, now.month, 1)
    query_sql = f"select count(distinct a.cve_id) from inventory_patch as a left join patch as b on a.cve_id = b.cve_id " \
                f"where b.regular_time > '{this_month_start}'"
    print(query_sql)
    now = datetime.datetime.now()
    res = mysql_util.exec_sql_new(query_sql)
    count = res[0][0]
    save_new_data("month_regular_patch_sum", count)


def leak_inventory_top5():
    """
    漏洞资产top5
    :return:
    """
    query_sql = "select ip, hostname , unpatch_num from inventory order by unpatch_num desc limit 5"
    res_list = mysql_util.exec_sql_new(query_sql)
    res_dict_list = []
    for res in res_list:
        show_res = {
            'ip': res[0],
            'hostname': res[1],
            'num': res[2]
        }
        res_dict_list.append(show_res)
    save_new_data("leak_inventory_top5", json.dumps(res_dict_list))


def leak_inventory_group_top5():
    """
    漏洞资产组top5
    :return:
    """
    pass


def patch_update():
    """
    补丁更新记录
    :return:
    """
    pass


def inventory_safe_distribute():
    """
    资产风险分布
    :return:
    """
    query_sql = "select safe_lv, count(1) from inventory GROUP BY safe_lv"
    res_list = mysql_util.exec_sql_new(query_sql)
    res_dict_list = []
    for res in res_list:
        show_res = {
            'safe_lv': res[0],
            'num': res[1]
        }
        res_dict_list.append(show_res)
    save_new_data("inventory_safe_distribute", json.dumps(res_dict_list))


def unpatch_top10():
    """
    未打补丁top10
    :return:
    """
    query_sql = "select cve_id , count(1) as cve_count from inventory_patch GROUP BY cve_id order by cve_count desc limit 10"
    res_list = mysql_util.exec_sql_new(query_sql)
    res_dict_list = []
    for res in res_list:
        show_res = {
            'cve_id': res[0],
            'num': res[1]
        }
        res_dict_list.append(show_res)
    save_new_data("unpatch_top10", json.dumps(res_dict_list))
    pass


def system_monitor():
    """
    系统资源监控
    :return:
    """
    pass


if __name__ == '__main__':
    overview_task()

